/**
 * 将字符串中的全角字符转换为半角字符。
 * @param {String} str 
 * @returns 
 */
function DBC2SBC(str)
{
    var result = "";
    for(var i = 0; i < str.length; i++)
    {
        code = str.charCodeAt(i);    //获取当前字符的unicode编码
        if (code >= 65281 && code <= 65373)   //在这个unicode编码范围中的是所有的英文字母已经各种字符
        {
            var d = str.charCodeAt(i) - 65248;
            result += String.fromCharCode(d);   //把全角字符的unicode编码转换为对应半角字符的unicode码
        }
        else if (code == 12288)//空格
        {
            var d = str.charCodeAt(i) - 12288 + 32;
            result += String.fromCharCode(d);
        }
        else
        {
            result += str.charAt(i);
        }
    }
    return result;
}

/**
 * 去除首尾空格符
 * @param {String} str 
 * @returns 
 */
function trimStr(str){
    return str.replace(/(^\s*)|(\s*$)/g,"");
}

var srcDataAddressX = 0
var srcDataAddressY = 0
var srcDataColumnsCount = 0
var srcDataRowsCount = 0
var srcSheetName = 0
var srcRange

var findAddressX = 0
var findAddressY = 0
var findRowsCount = 0  // 只支持一列查找
var findSheetName = 0
var findRange

var dataSourceWorkbook;
var findWorkbook;

function onbuttonclick(idStr)
{
    if (typeof (wps.Enum) != "object") { // 如果没有内置枚举值
        wps.Enum = WPS_Enum
    }
    switch(idStr)
    {
        case "setDataSource":{
                //alert("setDataSource")
                dataSourceWorkbook = wps.EtApplication().ActiveWorkbook;
                var aSheet = dataSourceWorkbook.ActiveSheet;
                srcSheetName = aSheet.Name;
                var txtDataSource = document.getElementById("txtDataSource");
                let s = wps.Application.Selection;
                if(s.Areas.Count != 1){
                    alert("用于查找的源数据区域必须是连续的！");
                    return;
                }
                var srcAddress = ("From: [" + dataSourceWorkbook.Name + "] - " + srcSheetName + "\r\nRow: " + s.Row + ", Rows Count: " + s.Rows.Count + "\r\n" +
                                  "Column: " + s.Column + ", Columns Count: " + s.Columns.Count);
                //alert(srcAddress)
                txtDataSource.value = srcAddress;
                srcRange = s;
                
                srcDataAddressY = s.Row;
                srcDataAddressX = s.Column;
                srcDataColumnsCount = s.Columns.Count;
                srcDataRowsCount = s.Rows.Count;

                // 添加CheckBoxList
                var div = document.getElementById("resultDiv"); 
                div.innerHTML = '';
                var sel = document.getElementById("destColumn");
                sel.innerHTML = '';
                
                for(var i = 0; i < s.Columns.Count; i++){  
                    // 加入复选框  
                    var checkBox = document.createElement("input");  
                    checkBox.setAttribute("type","checkbox");  
                    checkBox.setAttribute("id", "ckx_" + i);  
                    checkBox.setAttribute("name", "resultCheckBox");  
                    checkBox.setAttribute("index", i.toString());
                    checkBox.checked = true;
                  
                    var p=document.createElement("p");  
                    p.appendChild(checkBox);  
                    var x = srcDataAddressX + i;
                    var y = srcDataAddressY;
                    checkBox.value = i;   // 记下列在源数据中的标号
                    var headerCell = aSheet.Cells.Item(y, x);
                    //alert(headerCell.Text)
                    p.appendChild(document.createTextNode(headerCell.Text));  
                    //li.appendChild(document.createTextNode('xxx' + i));
                    div.appendChild(p);  

                    sel.options.add(new Option(headerCell.Text, i));  // Text, Value
                }
                break
            }
        case "setFindColumn":{              
                //alert("setFindColumn")
                findWorkbook = wps.EtApplication().ActiveWorkbook;
                var aSheet = findWorkbook.ActiveSheet;
                findSheetName = aSheet.Name;

                var findColumn = document.getElementById("txtFindColumn")
                let s = wps.Application.Selection
                if(s.Areas.Count != 1){
                    alert("用作查找条件的数据区域必须是连续的！");
                    return;
                }

                if(s.Rows.Count < 1){
                    alert("用作查找条件的数据区域不能少于1行！");  // 其实根本不可能少于1行
                    return;
                }

                var findColumnAddress = ("Find: [" + findWorkbook.Name + "] - " + findSheetName + "\r\nRow: " + s.Row + ", Rows Count: " + s.Rows.Count + "\r\n" +
                                  "Column: " + s.Column + ", Columns Count: " + s.Columns.Count );
                //alert(findColumnAddress)

                var conditionColIndexInput = document.getElementById('conditionColIndex');
                if(conditionColIndexInput){
                    conditionColIndexInput.value = s.Columns.Count;
                }
                findColumn.value = findColumnAddress

                findAddressX = s.Column;
                findAddressY = s.Row;
                findRowsCount = s.Rows.Count; 
                findColumnsCount = s.Columns.Count;
                findRange = s;

                var sel = document.getElementById("conditionColIndex");
                sel.innerHTML = '';
                for(var i = 0; i < s.Columns.Count; i++){
                    var headerCell = aSheet.Cells.Item(findAddressY, findAddressX + i);
                    sel.options.add(new Option(headerCell.Text, i + 1));
                }

                if(sel.options.length > 0){
                    sel.value = sel.options.length;
                }
                break
            }
        case "checkAllFields":{
                var div = document.getElementById("resultDiv");
                var elements = div.getElementsByTagName("input");
                for(var i = 0; i < elements.length; i++){
                    var element = elements[i];
                    var attr = element.getAttribute("type");
                    if(attr == null || attr != "checkbox")continue;
                    
                    element.checked = true;
                }
                break;
            }
        case "unCheckAllFields":{
                var div = document.getElementById("resultDiv");
                var elements = div.getElementsByTagName("input");
                for(var i = 0; i < elements.length; i++){
                    var element = elements[i];
                    var attr = element.getAttribute("type");
                    if(attr == null || attr != "checkbox")continue;

                    element.checked = false;
                }
                break;
            }
        case "switchCheckAllFields":{
                var div = document.getElementById("resultDiv");
                var elements = div.getElementsByTagName("input");
                for(var i = 0; i < elements.length; i++){
                    var element = elements[i];
                    var attr = element.getAttribute("type");
                    if(attr == null || attr != "checkbox")continue;

                    element.checked = !element.checked;
                }
                break;
            }
        case "btnFind":{
                //alert("abcd")
                var ckxSourceDataWithTitle = document.getElementById("ckxSourceDataWithTitle")
                var ckxFindWithTitle = document.getElementById("ckxFindWithTitle")

                if(dataSourceWorkbook == undefined){
                    alert("未设置数据源表！");
                    return;
                }
                if(findWorkbook == undefined){
                    alert("未设置查找条件表！");
                    return;
                }
                var srcSheets = dataSourceWorkbook.Sheets;                
                var srcSheet;
                for(j=1; j<=srcSheets.Count; j++){   // 注意：这个索引是从1开始的！！！
                    var ss = srcSheets.Item(j);
                    if(ss==null)continue;
                    if(ss.Name == srcSheetName){
                        srcSheet = ss;
                        break;
                    }
                }                
                if(srcSheet == undefined){
                    alert('未找到源数据表！');
                    return;
                }
                var findSheets = findWorkbook.Sheets;
                var fndSheet;
                for(j=1; j<=findSheets.Count; j++){   // 注意：这个索引是从1开始的！！！
                    var fs = findSheets.Item(j);
                    if(fs==null)continue;
                    if(fs.Name == findSheetName){
                        fndSheet = fs;
                        break;
                    }
                }
                if(fndSheet == undefined){
                    alert('未找到查找条件所在数据表！');
                    return;
                }

                var srcOffcet = 0;
                if(ckxSourceDataWithTitle.checked && srcDataRowsCount > 1){
                    srcOffcet = 1;
                }
                var fndOffcet = 0;
                if(ckxFindWithTitle.checked && findRowsCount > 1){
                    fndOffcet = 1;
                }

                // 注意:坐标是先行后列!!!不是先X后Y!!!
                var srcTitleRangeArr = srcSheet.Range(srcSheet.Cells.Item(srcDataAddressY + srcOffcet - 1, srcDataAddressX), srcSheet.Cells.Item(srcDataAddressY + srcOffcet - 1, srcDataAddressX + srcDataColumnsCount - 1)).Value2;
                var srcRange = srcSheet.Range(srcSheet.Cells.Item(srcDataAddressY + srcOffcet, srcDataAddressX), srcSheet.Cells.Item(srcDataAddressY + srcDataRowsCount - 1, srcDataAddressX + srcDataColumnsCount - 1));
                var srcArray = srcRange.Value2;
                var fndRange = fndSheet.Range(fndSheet.Cells.Item(findAddressY + fndOffcet, findAddressX), fndSheet.Cells.Item(findAddressY + findRowsCount - 1, findAddressX + findColumnsCount - 1));  // 注意：查找条件列暂时只支持一列
                var fndArray = fndRange.Value2;
                if(srcArray == null){
                    alert("指定的源数据区域中没有可用的数据!");
                    return;
                }
                if(fndArray == null){
                    alert("指定查找条件区域没有可用的数据！");
                    return;
                }

                // 取出用户在主界面上选择的、要出现在结果中的各列的索引
                var ckxes = document.getElementsByName("resultCheckBox");
                var colIndexArr = [];
                for(ii = 0; ii < ckxes.length; ii++){
                    if(ckxes[ii].checked){
                        colIndexArr.push(ckxes[ii].getAttribute("index"));
                    }
                }

                // 取出用户设置的查找目标列索引（查找时按查找条件列和这个索引指向源数据列进行比对）
                var sel = document.getElementById("destColumn");
                var col = parseInt(sel.value);

                var conditionColIndexInput = document.getElementById('conditionColIndex');
                var conditionColIndex = 1;
                if(conditionColIndexInput){
                    conditionColIndex = parseInt(conditionColIndexInput.value);
                }

                if(conditionColIndex == null || conditionColIndex == undefined || 
                    conditionColIndex == NaN || conditionColIndex < 1){
                        alert("未正确设置查找条件所在列！");
                        return;
                    }

                if(fndArray.length < 1){
                    alert("没有设置有效的查找条件！");
                    return;
                }

                if(conditionColIndex > fndArray[0].length){
                    alert("查找条件列数值过大，超过了条件区域的最大列数！");
                    return;
                }

                var rowIndexes = [];
                var emptyIndexes = [];
                var findedArray = [];
                var finded = false;
                for(i = 0; i < fndArray.length; i++){  // 查找条件列中每个单元格逐次循环
                    var v = fndArray[i][conditionColIndex - 1];   // 数组从0开始算
                    if(v == null) continue;                // 查找条件中可能有空单元格（例如合并单元格做的表头）
                    v = trimStr(DBC2SBC(v.toString()));
                    for(j = 0; j < srcArray.length; j++){   // 逐行循环
                        var resultRowArray = [];  // 注意：这个每行都要重建
                        var srcv = srcArray[j][col];
                        if(srcv == null) continue;         // 用于查找的数据源表中也可能有空单元格
                        srcv = trimStr(DBC2SBC(srcv.toString()));
                        if(srcv.toLowerCase() == v.toLowerCase()){
                            resultRowArray.push(...fndArray[i]);  // 为什么这里不直接用 v ？会变成嵌套数组的！！
                            var rowArray = srcArray[j];
                            for(l = 0; l < rowArray.length; l++){
                                if(colIndexArr.indexOf(l.toString()) >= 0){
                                    resultRowArray.push(rowArray[l]);
                                }
                            }
                            findedArray.push(resultRowArray);
                            finded = true;
                        }
                        // 为什么这里不用 break; ？这是为了防止查找目标列中存在重复的值——这样可以保证所有重复的值都能被找到！！
                    }
                    
                    if(finded == false){
                        var emptyRowArray = [];
                        emptyRowArray.push(...fndArray[i]);
                        emptyRowArray.push("<未找到>")
                        for(var c = 1; c < colIndexArr.length; c++){
                            emptyRowArray.push("'-");
                        }
                        findedArray.push(emptyRowArray);
                        emptyIndexes.push(i);
                    }
                    else finded = false;

                    rowIndexes.push(j);   // 现在要显示没找到，所以所有 j 都要加进去，不然最后几个显示不出来
                    // alert(v + ":" + finded + "; 数组长度:" + findedArray.length);
                }

                // 找到的数据放在新表中
                var newSheet = dataSourceWorkbook.Sheets.Add();   // 放在查找条件所在的工作簿中不方便，改为数据源所在的工作簿才好复制数据
                newSheet.Cells.NumberFormatLocal = "@";   // 必须设置为文本格式

                //注意，首行通常是标题行                
                // 无论有没有标题，第一行都要空下来，这样做起来简单得很——而且方便用户自行添加标题行各字段的标题文本。
                if(ckxFindWithTitle.checked){
                    var fndTitleRange = fndSheet.Range(fndSheet.Cells.Item(findAddressY + fndOffcet - 1, findAddressX),
                                                       fndSheet.Cells.Item(findAddressY + fndOffcet - 1, findAddressX + findColumnsCount -1));
                    newSheet.Range(newSheet.Cells.Item(1, 1), newSheet.Cells.Item(1, findColumnsCount)).Value2 = fndTitleRange.Value2;
                }
    
                if(ckxSourceDataWithTitle.checked){
                    var fndTitlesArr = [];
                    for(k = 0; k < srcTitleRangeArr[0].length; k++){
                        if(colIndexArr.indexOf(k.toString()) >= 0){
                            var fieldName = srcTitleRangeArr[0][k];
                            fndTitlesArr.push(fieldName);
                        }
                    }
                }
                
                // 将查找条件的标题行和查找到的数据的标题行写到新表中（不保证没有重复数据）
                newSheet.Range(newSheet.Cells.Item(1, findColumnsCount + 1), newSheet.Cells.Item(1, findColumnsCount + colIndexArr.length)).Value2 = fndTitlesArr;
                
                // 将查找到的数据写到结果区域（结果是个二维数组，其中包括了查找条件那几列）
                newSheet.Range(newSheet.Cells.Item(2, 1), newSheet.Cells.Item(findedArray.length + 1, colIndexArr.length + findColumnsCount)).Value2 = findedArray;

                // 设置新表中“查找条件列”的背景色时，如果查找条件列不包括标题，就少设置一个单元格背景（不然看着难受）
                // var fndColumnBackgroundStartRowIndex = 2;
                // if(ckxFindWithTitle.checked){
                //     fndColumnBackgroundStartRowIndex = 1;
                // }else{
                //     newSheet.Cells.Item(1, 1).Value2 = "==条件==";
                // }
                // 不加个标题区分度不够！
                var fndColumnBackgroundStartRowIndex = 1;
                if(ckxFindWithTitle.checked != true){
                    newSheet.Cells.Item(1, conditionColIndex).Value2 = "【条件】";
                }

                // 查找条件列着色
                var rng = newSheet.Range(newSheet.Cells.Item(fndColumnBackgroundStartRowIndex, 1), newSheet.Cells.Item(findedArray.length + 1, findColumnsCount));
                rng.Interior.Pattern = 1;  //xlPatternSolid;
                rng.Interior.ThemeColor = 6;
                rng.Interior.TintAndShade = 0.8;
                rng.Interior.PatternColorIndex = -4105;     
                
                // 检查是否在查找源数据中找到重复的记录
                // 注意：不会考虑查找条件列中是否存在重复记录，更不会考虑查找条件中的重复值是否连续！
                var preFindValue = null;
                for(i = 0; i < findedArray.length; i++){
                    if(preFindValue == null){
                        preFindValue = newSheet.Cells.Item(i + 2, 1).Value2.toString().toLowerCase();
                        continue;
                    }
                    var curCell = newSheet.Cells.Item(i + 2, 1);
                    var curFindValue = curCell.Value2.toString().toLowerCase();
                    if(preFindValue == curFindValue){
                        var erng = newSheet.Range(curCell, newSheet.Cells.Item(i + 2, colIndexArr.length + 1));
                        erng.Interior.Pattern = 1;
                        erng.Interior.Color = 5287936;
                        erng.Interior.TintAndShade = 0;
                        erng.PatternColorIndex = -4105
                        erng.Font.ThemeColor = 1;
                        erng.Font.TintAndShade = 0;
                    }
                    preFindValue = curFindValue;
                }

                // 将 <未找到> 单元格都设置为黄色背景
                for(i = 0; i < findedArray.length; i++){
                    // alert(emptyIndexes[i]);
                    var erng = newSheet.Range(newSheet.Cells.Item(i + 2, findColumnsCount + 1), newSheet.Cells.Item(i + 2, findColumnsCount + 1));
                    if(erng.Value2.toString() == "<未找到>"){
                        // 加 2 是因为：数组索引从0开始，要加1；第1行要用作标题，又加1。
                        erng.Interior.Pattern = 1;
                        erng.Interior.Color = 65535;
                        erng.Interior.TintAndShade = 0;
                        erng.Font.Color = 255;
                        erng.Font.TintAndShade = 0;
                    }
                }

                newSheet.Cells.Select();
                newSheet.Cells.Columns.AutoFit();

                var msg = "查找条件共 " + fndArray.length + " 条，找到 " + (findedArray.length - emptyIndexes.length) + " 条，未找到 " + emptyIndexes.length +
                "条。\n所有无匹配结果的记录均显示为黄底红字、值为 <未找到> 字样的单元格。" ; 
                if(findedArray.length > fndArray.length){
                    msg += "\r\n\r\n 注意：查找时发现有连续重复记录！！以绿色背景标记。（本功能不会考虑查找条件是否有重复记录，只会看查找目标数据中有无重复！）";
                }
                alert(msg);
                break;
            }
    }
}


window.onload = ()=>{
    // var xmlReq = WpsInvoke.CreateXHR();
    // var url = location.origin + "/.debugTemp/NotifyDemoUrl"
    // xmlReq.open("GET", url);
    // xmlReq.onload = function (res) {
    //     var node = document.getElementById("DemoSpan");
    //     window.document.getElementById("DemoSpan").innerHTML = res.target.responseText;
    // };
    // xmlReq.send();
}